Перейти к основному содержимому

Первые шаги с SQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВНЕ ДЛЯ НОВИЧКОВВ РАЗРАБОТКЕ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Установка системы PostgreSQL​

Процесс установки СУБД требует выполнения действий в зависимости от операционной системы пользователя. Для работы с базой данных необходим серверный компонент и клиентские утилиты.

Требования к системе​

  • Операционная система: Windows, Linux (Debian/Ubuntu) или macOS.
  • Права администратора на машине.
  • Доступ к интернету для загрузки установочных пакетов.

Алгоритм установки​

Вариант А: Установка на Windows​

  1. Перейдите на официальный сайт проекта PostgreSQL.
  2. Скачайте последний стабильный дистрибутив для Windows.
  3. Запустите файл установки.
  4. Следуйте инструкциям мастера установки:
    • Укажите путь к директории установки.
    • Задайте пароль для суперпользователя postgres. Этот пароль потребуется для всех последующих подключений.
    • Выберите порт подключения (стандартное значение — 5432).
    • Установите компоненты «pgAdmin» и «Command Line Tools». pgAdmin предоставляет графический интерфейс, а Command Line Tools позволяют работать через терминал.
  5. Завершите установку и запустите приложение pgAdmin.

Вариант Б: Установка на Linux (на примере Ubuntu/Debian)​

Откройте терминал и выполните следующие команды:

sudo apt update
sudo apt install postgresql postgresql-contrib

После завершения установки проверьте статус службы:

sudo systemctl status postgresql

Для входа в систему используйте команду:

sudo -u postgres psql

Вариант В: Использование Docker (универсальный способ)​

Создание контейнера с базой данных позволяет изолировать среду разработки:

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres:latest

Подключение к контейнеру выполняется командой:

docker exec -it my-postgres psql -U postgres

Создание базы данных​

База данных представляет собой логическое хранилище для таблиц, индексов и других объектов. Создание БД выполняется через системную команду или графический интерфейс.

Командный способ​

Выполните команду в терминале или через клиент psql:

CREATE DATABASE company_db;

Графический способ (pgAdmin)​

  1. Откройте pgAdmin и раскройте узел «Servers».
  2. Нажмите правой кнопкой мыши на пункт «Databases».
  3. Выберите «Create» -> «Database...».
  4. В открывшемся окне введите имя company_db.
  5. Укажите владельца базы (обычно postgres).
  6. Нажмите «Save».

Важно помнить, что каждая новая база данных имеет собственную схему по умолчанию public, куда будут помещаться созданные объекты.

Создание таблицы​

Таблица — это основная структура для хранения данных, состоящая из строк и столбцов. Определение структуры таблицы включает указание имен колонок и их типов данных.

Синтаксис создания таблицы​

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2),
    department_id INTEGER
);

Анализ компонентов определения​

  • id: Автоинкрементный идентификатор. Тип SERIAL автоматически создает последовательность и назначает уникальные значения.
  • first_name, last_name: Текстовые поля фиксированной длины. Ограничение NOT NULL гарантирует заполненность.
  • email: Поле для адреса электронной почты. Ограничение UNIQUE запрещает дублирование значений.
  • hire_date: Дата найма. Параметр DEFAULT CURRENT_DATE подставляет текущую дату при отсутствии явного значения.
  • salary: Числовое поле с двумя знаками после запятой.
  • department_id: Целочисленное поле для связи с отделами.

Добавление ограничений и индексов​

Ограничения обеспечивают целостность данных, а индексы ускоряют поиск информации.

Добавление внешнего ключа​

Внешний ключ связывает строку одной таблицы со строкой другой. Это обеспечивает ссылочную целостность.

Предположим, существует таблица departments:

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Добавим связь между таблицами employees и departments:

ALTER TABLE employees 
ADD CONSTRAINT fk_department 
FOREIGN KEY (department_id) REFERENCES departments(id);

Создание индекса​

Индекс — это структура данных, которая ускоряет операции выборки. Индексируются часто используемые поля поиска или сортировки.

Создание индекса по фамилии сотрудников:

CREATE INDEX idx_employees_last_name ON employees(last_name);

Создание составного индекса для ускорения поиска по отделу и дате найма:

CREATE INDEX idx_dept_hire ON employees(department_id, hire_date);

Использование индексов снижает время выполнения запросов, особенно на больших объемах данных.

Выполнение SQL CRUD запросов​

CRUD (Create, Read, Update, Delete) — набор операций для управления данными.

Создание записей (Create)​

Вставка новых сотрудников в таблицу:

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES 
('Ivan', 'Ivanov', 'ivanov@example.com', 75000.00, 1),
('Maria', 'Petrova', 'petrova@example.com', 82000.00, 2),
('Alexey', 'Sidorov', 'sidorov@example.com', 65000.00, 1);

Вставка данных об отделах:

INSERT INTO departments (name)
VALUES ('IT Department'), ('HR Department');

Чтение данных (Read)​

Выборка всех записей:

SELECT * FROM employees;

Выборка конкретных колонок с фильтрацией:

SELECT first_name, last_name, salary 
FROM employees 
WHERE salary > 70000;

Сортировка результатов:

SELECT * FROM employees ORDER BY salary DESC;

Обновление данных (Update)​

Изменение зарплаты сотрудника:

UPDATE employees 
SET salary = 80000.00 
WHERE id = 1;

Обновление нескольких полей одновременно:

UPDATE employees 
SET email = 'new_email@example.com', 
    hire_date = '2024-01-01' 
WHERE id = 2;

Удаление данных (Delete)​

Удаление конкретного сотрудника:

DELETE FROM employees WHERE id = 3;

Удаление всех записей из таблицы (осторожно):

TRUNCATE TABLE employees;

Создание представлений (VIEW)​

Представление — это виртуальная таблица, созданная на основе результата запроса. Представления не хранят данные физически, а вычисляют их при обращении.

Простое представление​

Создание представления со списком сотрудников и их зарплатами:

CREATE VIEW employee_salary_view AS
SELECT 
    e.first_name,
    e.last_name,
    d.name AS department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;

Использование представления:

SELECT * FROM employee_salary_view WHERE salary > 75000;

Преимущества использования VIEW​

  • Упрощение сложных запросов.
  • Сокрытие деталей реализации физических таблиц.
  • Контроль доступа к определенным колонкам.
  • Стандартизация логики выборки данных.

Создание триггеров и процедур с агрегатными функциями и JOIN​

Триггеры автоматически выполняют действия при изменении данных. Процедуры позволяют инкапсулировать сложную логику и использовать агрегатные функции.

Создание функции с агрегатными функциями​

Функция рассчитывает среднюю зарплату в отделе.

CREATE OR REPLACE FUNCTION get_avg_salary_by_department(p_dept_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    avg_sal NUMERIC;
BEGIN
    SELECT AVG(salary) INTO avg_sal
    FROM employees
    WHERE department_id = p_dept_id;
    
    RETURN avg_sal;
END;
$$ LANGUAGE plpgsql;

Вызов функции:

SELECT get_avg_salary_by_department(1);

Создание процедуры с использованием JOIN​

Процедура обновляет статистику отдела при добавлении нового сотрудника.

CREATE OR REPLACE PROCEDURE update_department_stats()
LANGUAGE plpgsql
AS $$
DECLARE
    dept_record RECORD;
BEGIN
    FOR dept_record IN 
        SELECT d.id, d.name, COUNT(e.id) as emp_count, SUM(e.salary) as total_salary
        FROM departments d
        LEFT JOIN employees e ON d.id = e.department_id
        GROUP BY d.id, d.name
    LOOP
        RAISE NOTICE 'Отдел: %, Количество сотрудников: %, Общий фонд: %', 
                     dept_record.name, dept_record.emp_count, dept_record.total_salary;
        
        -- Здесь можно добавить логику записи статистики в отдельную таблицу
        -- INSERT INTO dept_statistics ...
    END LOOP;
END;
$$;

Запуск процедуры:

CALL update_department_stats();

Создание триггера​

Триггер автоматически проверяет корректность зарплаты перед вставкой записи. Зарплата не может быть отрицательной.

CREATE OR REPLACE FUNCTION check_salary_positive()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Зарплата не может быть отрицательной!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary_positive();

Попробуйте выполнить вставку с отрицательной зарплатой:

INSERT INTO employees (first_name, last_name, salary)
VALUES ('Test', 'User', -5000);
-- Система вернет ошибку исключения.
Освоение главы0%